import requests
from lxml import etree
import logging
import pandas as pd
from tenacity import retry, stop_after_attempt, wait_fixed
from tqdm import tqdm
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer

# 设置日志配置
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# 创建一个日志记录器
logger = logging.getLogger(__name__)

# 数据库模型定义
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class CarData(Base):
    __tablename__ = 'car_data'

    id = Column(Integer, primary_key=True, autoincrement=True)
    official_price = Column(String(255))
    sub_brand_name = Column(String(255))
    jb = Column(String(255))
    fuel_form = Column(String(255))
    market_time = Column(String(255))
    engine_description = Column(String(255))
    energy_elect_max_power = Column(String(255))
    energy_elect_max_torque = Column(String(255))
    gearbox_description = Column(String(255))
    length_width_height = Column(String(255))
    car_body_struct = Column(String(255))
    max_speed = Column(String(255))
    acceleration_time = Column(String(255))
    wltc_fuel_comprehensive = Column(String(255))
    period = Column(String(255))
    maintain_cost = Column(String(255))

# 数据抓取和解析函数
@retry(stop=stop_after_attempt(3), wait=wait_fixed(2))
def fetch_data(url):
    """获取网页数据并解析"""
    try:
        response = requests.get(url)
        response.raise_for_status()  # 确保响应成功
        logger.info(f"Successfully fetched data from: {url}")
        return etree.HTML(response.text)
    except requests.exceptions.RequestException as e:
        logger.error(f"Request failed for {url}: {e}")
        return None

def extract_data(tree):
    """从 HTML 树中提取数据"""
    data = {}
    try:
        fields = [
            ('official_price', '//div[@data-row-anchor="official_price"]//div/text()'),
            ('sub_brand_name', '//div[@data-row-anchor="sub_brand_name"]//div/text()'),
            ('jb', '//div[@data-row-anchor="jb"]//div/text()'),
            ('fuel_form', '//div[@data-row-anchor="fuel_form"]//div/text()'),
            ('market_time', '//div[@data-row-anchor="market_time"]//div/text()'),
            ('engine_description', '//div[@data-row-anchor="engine_description"]//div/text()'),
            ('energy_elect_max_power', '//div[@data-row-anchor="energy_elect_max_power"]//div/text()'),
            ('energy_elect_max_torque', '//div[@data-row-anchor="energy_elect_max_torque"]//div/text()'),
            ('gearbox_description', '//div[@data-row-anchor="gearbox_description"]//div/text()'),
            ('length_width_height', '//div[@data-row-anchor="length_width_height"]//div/text()'),
            ('car_body_struct', '//div[@data-row-anchor="car_body_struct"]//div/text()'),
            ('max_speed', '//div[@data-row-anchor="max_speed"]//div/text()'),
            ('acceleration_time', '//div[@data-row-anchor="acceleration_time"]//div/text()'),
            ('wltc_fuel_comprehensive', '//div[@data-row-anchor="wltc_fuel_comprehensive"]//div/text()'),
            ('period', '//div[@data-row-anchor="period"]//div/text()'),
            ('maintain_cost', '//div[@data-row-anchor="maintain_cost"]//div/text()')
        ]
        for key, xpath in fields:
            value = tree.xpath(xpath)
            if not value:
                logger.warning(f"Missing data for {key}. Filling with '无'.")
                value = ["无"]  # 如果数据为空，填充"无"
            data[key] = value
        return data
    except Exception as e:
        logger.error(f"Error extracting data: {e}")
        return None

def save_to_db(data):
    """将数据保存到数据库"""
    try:
        # 设置数据库连接
        engine = create_engine('mysql+pymysql://root:17884873770@localhost:3306/car_data')
        Session = sessionmaker(bind=engine)
        session = Session()

        for i in range(len(data['official_price'])):
            car = CarData(
                official_price=data['official_price'][i],
                sub_brand_name=data['sub_brand_name'][i],
                jb=data['jb'][i],
                fuel_form=data['fuel_form'][i],
                market_time=data['market_time'][i],
                engine_description=data['engine_description'][i],
                energy_elect_max_power=data['energy_elect_max_power'][i],
                energy_elect_max_torque=data['energy_elect_max_torque'][i],
                gearbox_description=data['gearbox_description'][i],
                length_width_height=data['length_width_height'][i],
                car_body_struct=data['car_body_struct'][i],
                max_speed=data['max_speed'][i],
                acceleration_time=data['acceleration_time'][i],
                wltc_fuel_comprehensive=data['wltc_fuel_comprehensive'][i],
                period=data['period'][i],
                maintain_cost=data['maintain_cost'][i]
            )
            session.add(car)

        session.commit()
        logger.info(f"Data saved to the database successfully.")

    except Exception as e:
        logger.error(f"Error saving data to database: {e}")

def print_valid_data(data):
    """打印有效的数据"""
    if data:
        n = len(data['official_price'])  # 获取列表长度
        logger.info(f"Found {n} entries.")
        for i in range(n):
            try:
                print(f"{data['official_price'][i]} {data['sub_brand_name'][i]} {data['jb'][i]} "
                      f"{data['fuel_form'][i]} {data['market_time'][i]} {data['engine_description'][i]} "
                      f"{data['energy_elect_max_power'][i]} {data['energy_elect_max_torque'][i]} "
                      f"{data['gearbox_description'][i]} {data['length_width_height'][i]} "
                      f"{data['car_body_struct'][i]} {data['max_speed'][i]} {data['acceleration_time'][i]} "
                      f"{data['wltc_fuel_comprehensive'][i]} {data['period'][i]} {data['maintain_cost'][i]}")
            except IndexError:
                logger.warning(f"Missing data at index {i}. Skipping this entry.")
    else:
        logger.warning("No valid data to print.")

def fetch_and_process():
    """主程序，进行数据抓取与处理"""
    data_all = {
        'official_price': [],
        'sub_brand_name': [],
        'jb': [],
        'fuel_form': [],
        'market_time': [],
        'engine_description': [],
        'energy_elect_max_power': [],
        'energy_elect_max_torque': [],
        'gearbox_description': [],
        'length_width_height': [],
        'car_body_struct': [],
        'max_speed': [],
        'acceleration_time': [],
        'wltc_fuel_comprehensive': [],
        'period': [],
        'maintain_cost': []
    }

    for i in tqdm(range(1, 10)):
        url = f'https://www.dongchedi.com/auto/params-carIds-x-{i}'
        logger.info(f"Fetching data from: {url}")

        tree = fetch_data(url)
        if tree:
            data = extract_data(tree)
            if data:
                # 将数据存入总字典
                for key in data_all:
                    data_all[key].extend(data[key])

    # 输出有效数据
    print_valid_data(data_all)
    # 保存数据到数据库
    save_to_db(data_all)

if __name__ == "__main__":
    fetch_and_process()
